﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections;

namespace Fize
{
    /// <summary>
    /// 通用数据库操作类
    /// </summary>
    public class DbHelper
    {
        //使用System.Data.Sql对象
        private string mConnectionString;
        protected CommandType myCommandType;	// 用于标志执行SQL或存储过程等
        protected SqlConnection Conn;

        /// <summary>
        /// 受保护的只读连接字符串属性
        /// 派生类可以继承该属性
        /// </summary>
        protected string ConnectionString
        {
            get
            {
                return mConnectionString;
            }
        }

        /// <summary>
		/// 参数化的构造函数，并建立数据库连接
		/// </summary>
		/// <param name="newConnectionString">数据库连接字符串</param>
        public DbHelper(string newConnectionString)
		{
			//
			// TODO: 在此处添加构造函数逻辑
			//
			mConnectionString = newConnectionString;
			Conn = new SqlConnection(mConnectionString);//创建数据库连接
			myCommandType = CommandType.StoredProcedure;
		}

        /// <summary>
        /// 建立一SqlCommand的原型，这一新的命令通常用于把
        /// SELECT语句的结果返回给DataSet或DataReader。
        /// </summary>
        /// <param name="cmdString">SQL命令或存储过程名</param>
        /// <param name="parameters">IDataParameter类的数组</param>
        /// <returns></returns>
        private SqlCommand BuildQueryCommand(string cmdString, IDataParameter[] parameters)
        {
            SqlCommand command = new SqlCommand(cmdString, Conn);
            command.CommandType = myCommandType;
            foreach (SqlParameter parameter in parameters)
            {
                command.Parameters.Add(parameter);
            }
            return command;
        }

        /// <summary>
        /// 建立一SqlCommand的原型，这一新的命令通常用于执行一命令，并返回数值
        /// </summary>
        /// <param name="cmdString">命令SQL或存储过程名</param>
        /// <param name="parameters">IDataParameter类的数组</param>
        /// <returns>最新的SqlCommand实例</returns>
        private SqlCommand BuildIntCommand(string cmdString, IDataParameter[] parameters)
        {
            SqlCommand command = BuildQueryCommand(cmdString, parameters);

            command.Parameters.Add(new SqlParameter("ReturnValue",
                SqlDbType.Int,
                4,		// Size
                ParameterDirection.ReturnValue,
                false,	// is nullable 
                0,		// byte precision
                0,		// byte scale
                string.Empty,
                DataRowVersion.Default,
                null));

            return command;
        }

        /// <summary>
        /// 建立一SqlCommand的原型，这一新的命令通常用于把
        /// SELECT语句的结果返回给DataSet或DataReader。
        /// </summary>
        /// <param name="cmdString">SQL命令或存储过程名</param>
        /// <param name="parameters">IDataParameter类的数组</param>
        /// <returns></returns>
        private SqlCommand BuildQueryCommand(string cmdString, IDataParameter[] parameters, CommandType commandType)
        {
            SqlCommand command = new SqlCommand(cmdString, Conn);
            command.CommandType = commandType;
            foreach (SqlParameter parameter in parameters)
            {
                command.Parameters.Add(parameter);
            }
            return command;
        }

        /// <summary>
        /// 建立一SqlCommand的原型，这一新的命令通常用于把
        /// SELECT语句的结果返回给DataSet或DataReader。
        /// </summary>
        /// <param name="cmdString">命令SQL或存储过程名</param>
        private SqlCommand BuildQueryCommand(string cmdString)
        {
            SqlCommand command = new SqlCommand(cmdString, Conn);
            //command.CommandType = myCommandType;
            return command;
        }

        /// <summary>
        /// 执行SqlCommand命令，返回整型ID号
        /// </summary>
        /// <param name="cmdString">执行的SQL语句</param>
        /// <param name="parameters">IDataParameter类的数组</param>
        /// <returns>返回整型ID号</returns>
        protected int RunCommand(string cmdString, IDataParameter[] parameters, out int rowsAffected)
        {
            int result;

            if (Conn.State == ConnectionState.Closed) Conn.Open();
            SqlCommand command = BuildIntCommand(cmdString, parameters);
            rowsAffected = command.ExecuteNonQuery();
            result = (int)command.Parameters["ReturnValue"].Value;
            Conn.Close();
            return result;
        }

        /// <summary>
        /// 执行SqlCommand命令，不返回值
        /// </summary>
        /// <param name="cmdString">执行的SQL语句</param>
        /// <param name="parameters">IDataParameter类的数组</param>
        protected void RunCommand(string cmdString, IDataParameter[] parameters)
        {
            if (Conn.State == ConnectionState.Closed) Conn.Open();
            SqlCommand command = BuildQueryCommand(cmdString, parameters);
            command.ExecuteNonQuery();
            Conn.Close();
        }

        /// <summary>
        /// 执行SqlCommand命令，不返回值
        /// </summary>
        /// <param name="cmdString">执行的SQL语句</param>
        /// <param name="parameters">IDataParameter类的数组</param>
        protected void RunCommand(string cmdString, IDataParameter[] parameters, CommandType commandType)
        {
            if (Conn.State == ConnectionState.Closed) Conn.Open();
            SqlCommand command = BuildQueryCommand(cmdString, parameters, commandType);
            command.ExecuteNonQuery();
            Conn.Close();
        }

        /// <summary>
        /// 执行SqlCommand命令，不返回值
        /// </summary>
        /// <param name="cmdString">执行的SQL语句</param>
        protected void RunCommand(string cmdString)
        {
            if (!(Conn.State == ConnectionState.Open))
                Conn.Open();
            // Start a local transaction.
            SqlTransaction myTrans = Conn.BeginTransaction();
            // Enlist the command in the current transaction.
            SqlCommand command = Conn.CreateCommand();
            command.Transaction = myTrans;
            try
            {
                command.CommandText = cmdString;
                command.ExecuteNonQuery();
                myTrans.Commit();
            }
            catch (Exception e)
            {
                myTrans.Rollback();
                Console.WriteLine("An exception of type " + e.GetType());
                return;
            }
            finally
            {
                if (Conn.State == ConnectionState.Open)
                    Conn.Close();
            }
        }

        /// <summary>
        /// 执行记录集更新
        /// </summary>
        /// <param name="cmdString">执行的SQL语句</param>
        /// <param name="ds">DataSet类型</param>
        /// <param name="tableName">数据表</param>
        protected void RunCommand(string cmdString, DataSet ds, string tableName)
        {
            if (!(Conn.State == ConnectionState.Open))
                Conn.Open();
            SqlDataAdapter Adpt = new SqlDataAdapter(cmdString, Conn);
            SqlCommandBuilder cb = new SqlCommandBuilder(Adpt);
            //Adpt.SelectCommand = BuildQueryCommand(cmdString);
            Adpt.Update(ds, tableName);
            ds.AcceptChanges();
            ds.Dispose();
            Adpt.Dispose();
        }

        /// <summary>
        /// 执行查询，并返回SqlDataReader查询记录
        /// </summary>
        /// <param name="cmdString">执行的SQL语句</param>
        /// <returns>返回SqlDataReader查询记录</returns>
        protected SqlDataReader GetDataReader(string cmdString)
        {
            SqlDataReader Dr;
            if (!(Conn.State == ConnectionState.Open))
                Conn.Open();
            SqlCommand command = BuildQueryCommand(cmdString);
            Dr = command.ExecuteReader(CommandBehavior.CloseConnection);
            //Conn.Close();
            return Dr;
        }

        /// <summary>
        /// 执行查询，并返回SqlDataReader查询记录
        /// </summary>
        /// <param name="cmdString">执行的SQL语句</param>
        /// <returns>返回SqlDataReader查询记录</returns>
        protected SqlDataReader GetDataReader(string cmdString, IDataParameter[] parameters)
        {
            SqlDataReader Dr;
            if (!(Conn.State == ConnectionState.Open))
                Conn.Open();
            SqlCommand command = BuildQueryCommand(cmdString, parameters);
            Dr = command.ExecuteReader(CommandBehavior.CloseConnection);
            //Conn.Close();
            return Dr;
        }

        /// <summary>
        /// 执行查询，并返回SqlDataReader查询记录
        /// </summary>
        /// <param name="cmdString">执行的SQL语句</param>
        /// <returns>返回SqlDataReader查询记录</returns>
        protected SqlDataReader GetDataReader(string cmdString, IDataParameter[] parameters, CommandType commandType)
        {
            SqlDataReader Dr;
            if (!(Conn.State == ConnectionState.Open))
                Conn.Open();
            SqlCommand command = BuildQueryCommand(cmdString, parameters, commandType);
            Dr = command.ExecuteReader(CommandBehavior.CloseConnection);
            //Conn.Close();
            return Dr;
        }

        /// <summary>
        /// 执行查询，并返回DataSet查询数据集
        /// </summary>
        /// <param name="cmdString">执行的SQL语句</param>
        /// <param name="tableName">要填充数据集的表名</param>
        /// <returns>返回DataSet查询数据集</returns>
        protected DataSet GetDataSet(string cmdString, string tableName)
        {
            DataSet ds = new DataSet();
            if (!(Conn.State == ConnectionState.Open))
                Conn.Open();
            SqlDataAdapter Adpt = new SqlDataAdapter();
            Adpt.SelectCommand = BuildQueryCommand(cmdString);
            Adpt.Fill(ds, tableName);
            Conn.Close();
            return ds;
        }

        /// <summary>
        /// 执行查询，并返回DataSet查询数据集
        /// </summary>
        /// <param name="cmdString">执行的SQL语句</param>
        /// <param name="parameters">IDataParameter类的数组</param>
        /// <param name="tableName">要填充数据集的表名</param>
        /// <returns>返回DataSet查询数据集</returns>
        protected DataSet GetDataSet(string cmdString, IDataParameter[] parameters, string tableName)
        {
            DataSet ds = new DataSet();
            if (!(Conn.State == ConnectionState.Open))
                Conn.Open();
            SqlDataAdapter Adpt = new SqlDataAdapter();
            Adpt.SelectCommand = BuildQueryCommand(cmdString, parameters);
            Adpt.Fill(ds, tableName);
            Conn.Close();
            return ds;
        }

        /// <summary>
        /// 执行查询，并返回DataSet查询数据集
        /// </summary>
        /// <param name="cmdString">执行的SQL语句</param>
        /// <param name="tableName">要填充数据集的表名</param>
        /// <param name="startRecord">开始记录数</param>
        /// <param name="maxRecords">最大查询记录数</param>
        /// <returns>返回DataSet查询数据集</returns>
        protected DataSet GetDataSet(string cmdString, string tableName, int startRecord, int maxRecords)
        {
            DataSet ds = new DataSet();
            SqlDataAdapter Adpt = new SqlDataAdapter();
            Adpt.SelectCommand = BuildQueryCommand(cmdString);
            Adpt.Fill(ds, startRecord, maxRecords, tableName);
            Conn.Close();
            return ds;
        }

        /// <summary>
        /// 执行查询，并返回DataSet查询数据集
        /// </summary>
        /// <param name="cmdString">执行的SQL语句</param>
        /// <param name="tableName">要填充数据集的表名</param>
        /// <param name="parameters">IDataParameter类的数组</param>
        /// <param name="startRecord">开始记录数</param>
        /// <param name="maxRecords">最大查询记录数</param>
        /// <returns>返回DataSet查询数据集</returns>
        protected DataSet GetDataSet(string cmdString, string tableName, IDataParameter[] parameters, int startRecord, int maxRecords)
        {
            DataSet ds = new DataSet();
            SqlDataAdapter Adpt = new SqlDataAdapter();
            Adpt.SelectCommand = BuildQueryCommand(cmdString, parameters);
            Adpt.Fill(ds, startRecord, maxRecords, tableName);
            Conn.Close();
            return ds;
        }

        /// <summary>
        /// 执行查询，并返回记录总数
        /// </summary>
        /// <param name="cmdString">执行的SQL语句，例如(select count(id) from tableName)</param>
        /// <returns>返回记录总数</returns>
        protected int GetRecordCount(string cmdString)
        {
            int RecordCount;
            if (!(Conn.State == ConnectionState.Open))
                Conn.Open();
            SqlCommand command = BuildQueryCommand(cmdString);
            RecordCount = (int)command.ExecuteScalar();
            Conn.Close();
            return RecordCount;
        }

        /// <summary>
        /// 关闭数据库
        /// </summary>
        protected void CloseConnection()
        {
            if (Conn.State == ConnectionState.Open)
            {
                Conn.Close();
            }
        }
    }
}
